﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;

namespace Jumpton.SFA.Server.BusinessRule.CallRoute
{
    public class RouteManager:BusinessLogic
    {
        public ObjectTable GetUserRoute(decimal userID)
        {
            return new ObjectTable(typeof(ROUTES), string.Format("this.USERID={0} order by Route_Number ", userID));
        }
        
        public ObjectTable GetUserRoute(string userID, string updatetime)
        {
            return new ObjectTable(typeof(ROUTES), string.Format("this.USERID={0} and LAST_UPDATE_TIME>'{1}' and ((ROUTE_NUMBER>0 and ROUTE_NUMBER<8) or (ROUTE_NUMBER>7 and VALID_DATE>'{2}'))", 
                userID, updatetime, DateTime.Now.ToString("yyyyMMddHHmmss")));
        }
        
        public void ExchangeRoute(decimal userID, decimal sourecRoute, decimal objectRoute)
        {
            decimal tmp;
            if (sourecRoute > objectRoute)
            {
                tmp = sourecRoute;
                sourecRoute = objectRoute;
                objectRoute = tmp;
            }

            string sql = string.Format(@"update ROUTE_CUSTOMERS set ROUTE_NUMBER='999' where USER_ID='{0}' and ROUTE_NUMBER='{1}'
                                         update ROUTE_CUSTOMERS set ROUTE_NUMBER='{1}', start_time=dateadd(day,-{3},start_time) where USER_ID='{0}' and ROUTE_NUMBER='{2}'
                                         update ROUTE_CUSTOMERS set ROUTE_NUMBER='{2}', start_time=dateadd(day,{3},start_time) where USER_ID='{0}' and ROUTE_NUMBER='999'"
                                         , userID, sourecRoute, objectRoute, objectRoute-sourecRoute);
            SqlData.ExecuteSql(sql);
        }

        public VISITS LoadVisits(decimal visitID)
        {
            return (VISITS)ObjectRow.Load(typeof(VISITS), string.Format("this.ID='{0}'", visitID));
        }

       


        public ROUTES Load(decimal routeID)
        {
            return (ROUTES)ObjectRow.Load(typeof(ROUTES), string.Format("this.ID='{0}'", routeID));
        }

       

      

        public static DataSet SearchCustomerCPR(decimal customerID, string productIDs)
        {
            return SqlData.OpenSP("sp_QueryCustomerCPR", customerID, productIDs);
        }



        public static int InitRoute(string userid, string updater)
        {
            return SqlData.ExecuteSP("SP_Init_ROUTE", userid, updater);
        }

        public static DataSet SearchCustomerProductCPR(decimal customerID,decimal ProductID,decimal domainID,int Number)
        {
            return SqlData.OpenSP("sp_QueryCustomerProductCPR", customerID, ProductID,domainID,Number);
        }

        public static DataSet SelectCustomerProduct(decimal customerID, decimal ProductID, decimal domainID, int Number)
        {
            DataSet ds = SqlData.OpenSP("sp_QueryCustomerProductCPR", customerID, ProductID, domainID, Number);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            string FieldName = "";
             //结构表
            DataTable dt = new DataTable("Struct");
            dt.Columns.Add(new DataColumn("MeasurementName"));
            dt.Columns.Add(new DataColumn("FieldName"));


            DataTable data = new DataTable("Data");
            data.Columns.Add(new DataColumn("ProductName"));
            data.Columns.Add(new DataColumn("Code"));
            data.Columns.Add(new DataColumn("VisitID",typeof(Decimal)));
            data.Columns.Add(new DataColumn("VISIT_TIME",typeof(DateTime)));
            data.Columns.Add(new DataColumn("Name"));

           

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                //构建出结构表中包含哪些指标字段
                if (ds.Tables[i].Rows.Count > 0)
                {
                    DataRow row = dt.NewRow();
                    row[0] = ds.Tables[i].Rows[0]["MeasurementName"];
                    FieldName = "Value" + (i + 1).ToString();
                    ds.Tables[i].Columns["Value"].ColumnName = FieldName;
                    row[1] = FieldName;
                    dt.Rows.Add(row);
                    data.Columns.Add(new DataColumn(FieldName));
                }

                foreach (DataRow dr in ds.Tables[i].Rows)
                {
                    DataRow CurrentRow = null;
                    DataRow[] rows = data.Select("VISITID=" + dr["VISITID"].ToString());
                    if (rows.Length == 0)
                    {
                        DataRow newRow = data.NewRow();
                        newRow["ProductName"] = dr["ProductName"];
                        newRow["Code"] = dr["Code"];
                        newRow["VISITID"] = dr["VISITID"];
                        newRow["Name"] = dr["Name"];
                        newRow["VISIT_TIME"] = dr["VISIT_TIME"];
                        data.Rows.Add(newRow);
                        CurrentRow = newRow;
                    }
                    else
                    {
                        CurrentRow = rows[0];
                    }
                    CurrentRow[FieldName] = dr[FieldName];


                }
            }
            ds.Tables.Clear();
            ds.Tables.Add(data);
            data.DefaultView.Sort = "VISIT_TIME DESC";
            //if (data.DefaultView.Count > Number)
            //{
            //    for (int i = Number; i < data.DefaultView.Count; i++)
            //    {
            //        data.DefaultView[i].Delete();
            //    }
            //    data.AcceptChanges();
            //}
            ds.Tables.Add(dt);
            return ds;      
        }

        public static DataSet SelectKPIByVisit(decimal VisitID, decimal domainID, int Number)
        {
            DataSet ds = SqlData.OpenSP("sp_QueryCPRByVisit", VisitID, domainID, Number);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            string FieldName = "";
            //结构表
            DataTable dt = new DataTable("Struct");
            dt.Columns.Add(new DataColumn("MeasurementName"));
            dt.Columns.Add(new DataColumn("FieldName"));

            DataTable data = new DataTable("Data");
            data.Columns.Add(new DataColumn("ProductName"));
            data.Columns.Add(new DataColumn("Code"));
            data.Columns.Add(new DataColumn("productid", typeof(Decimal)));
            data.Columns.Add(new DataColumn("VISIT_TIME", typeof(DateTime)));
            data.Columns.Add(new DataColumn("Name"));

            for (int i = 0; i < ds.Tables.Count; i++)
            {
                //构建出结构表中包含哪些指标字段
                if (ds.Tables[i].Rows.Count > 0)
                {
                    DataRow row = dt.NewRow();
                    row[0] = ds.Tables[i].Rows[0]["MeasurementName"];
                    FieldName = "Value" + (i + 1).ToString();
                    ds.Tables[i].Columns["Value"].ColumnName = FieldName;
                    row[1] = FieldName;
                    dt.Rows.Add(row);
                    data.Columns.Add(new DataColumn(FieldName));
                }

                foreach (DataRow dr in ds.Tables[i].Rows)
                {
                    DataRow CurrentRow = null;
                    DataRow[] rows = data.Select("productid=" + dr["productid"].ToString());

                    if (rows.Length == 0 )
                    {
                        DataRow newRow = data.NewRow();
                        newRow["ProductName"] = dr["ProductName"];
                        newRow["Code"] = dr["Code"];
                        newRow["productid"] = dr["productid"];
                        newRow["Name"] = dr["Name"];
                        newRow["VISIT_TIME"] = dr["VISIT_TIME"];
                        data.Rows.Add(newRow);
                        CurrentRow = newRow;
                    }
                    else
                    {
                        CurrentRow = rows[0];
                    }
                    CurrentRow[FieldName] = dr[FieldName];


                }
            }
            ds.Tables.Clear();
            ds.Tables.Add(data);
            data.DefaultView.Sort = "VISIT_TIME DESC";
            //if (data.DefaultView.Count > Number)
            //{
            //    for (int i = Number; i < data.DefaultView.Count; i++)
            //    {
            //        data.DefaultView[i].Delete();
            //    }
            //    data.AcceptChanges();
            //}
            ds.Tables.Add(dt);
            return ds;
        }

        public static DataSet SearchVisitHistory(decimal customerID)
        {
            return SqlData.OpenSP("sp_GetVisitByCustomerID", customerID);
        }

        public static DataSet SearchVisitHistoryByCustomerID(decimal customerID,decimal visitID)
        {
            return SqlData.OpenSP("sp_GetHistoryVisitByCustomerIDDictate", customerID.ToString(), visitID);
        }

        public static DataSet SearchVisitKPIByVISITID(decimal customerID, decimal visitID)
        {
            return SqlData.OpenSP("sp_GetVisitKPIByCustomerID", customerID, visitID);
        }

        public static DataSet GetVisitDetailByVisitID(decimal visitID)
        {
            return SqlData.OpenSP("sp_GetVisitDetailByVisitID", visitID);
        }

        /// <summary>
        /// 按照部门保存假日
        /// </summary>
        /// <param name="orgValues"></param>
        /// <param name="holiday"></param>
        public int SaveHoliday(string orgValues, string holiday)
        {
            return SqlData.ExecuteSP("up_SaveHoliday", orgValues, holiday);
        }

        /// <summary>
        /// 按照个人保存假日 
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="holiday"></param>
        /// <returns></returns>
        public int SaveHoliday(int userId, string holidays)
        {
            return SqlData.ExecuteSP("up_SaveHolidayUser", userId, holidays);
        }

        /// <summary>
        /// 按照部门获取假日
        /// </summary>
        /// <returns></returns>
        public DataTable GetHoliday()
        {
            string sql = "select distinct Date from Off_Day where Org_Id>0";
            return SqlData.OpenSql(sql).Tables[0];
        }

        /// <summary>
        /// 获取个人的假日
        /// </summary>
        /// <returns></returns>
        public DataTable GetMobileUserHoliday(int userId)
        {
            string sql = string.Format("select distinct Date from Off_Day where User_ID={0}", userId);
            return SqlData.OpenSql(sql).Tables[0];
        }

        /// <summary>
        /// 获取业代开始拜访线路的日期
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DateTime GetStartVisitDate(decimal userId)
        {
            string sql = string.Format("select ROUTE_START_TIME from Mobile_Users where User_ID={0}", userId);
            DataTable dt = SqlData.OpenSql(sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["ROUTE_START_TIME"] == DBNull.Value ? DateTime.Now.AddDays(1) : (DateTime)dt.Rows[0]["ROUTE_START_TIME"];
            }
            return DateTime.Now.AddDays(1);
        }


        public bool SaveRoutes(ROUTES route)
        {
            if(route!=null)
            {
                route.Save();
                return true;
            }
            return false;


        }

        public bool SearchVisitHistoryByCustomerID(int ID, string VALID_DATE, bool ACTIVE,bool ROUTE_FORCE)
        {
            return SqlData.ExecuteSP("[SP_SAVE_ROUTE]", ID, VALID_DATE, ACTIVE, ROUTE_FORCE) >= 0;
        }

        /// <summary>
        /// 获取到所有的路线或者指定路线的计划明细。
        /// </summary>
        /// <param name="RouteNumber">null表示所有的路线，具体的数值则是具体的线路号大于等于0且小于100</param>
        /// <param name="UserID">用户ID</param>
        /// <param name="Active">是否为有效的路线 null为获取全部路线 0是无效的路线 1是有效的路线</param>
        /// <returns></returns>
        public IDataReader GetAllRoutesCustomers(int? RouteNumber, int UserID, Boolean? Active)
        {
            String ActiveSQL= null;
            if(Active == null)
            {
                ActiveSQL="";
            }
            else
            {
                ActiveSQL = "AND T3.ACTIVE=" + (Active.Value ? "1" :"0");

                   
            }
            String SQL = string.Format(@"SELECT T2.ROUTE_NUMBER,T1.NAME,T3.DESCRIPTION,T3.VALID_DATE,T3.ROUTE_FORCE,T1.ID,T3.USER_ID FROM CUSTOMERS T1
                INNER JOIN ROUTE_CUSTOMERS T2 ON T1.ID=T2.CUSTOMER_ID
                INNER JOIN ROUTES T3 ON T2.ROUTE_NUMBER=T3.ROUTE_NUMBER
                WHERE T2.ACTIVE=1
                AND T2.USER_ID={0}
                AND T3.USER_ID={0}
                {1}
                {2}
                ORDER BY T2.ROUTE_NUMBER,SEQUENCE", UserID,ActiveSQL,
                    RouteNumber == null ? @"AND ((T3.ROUTE_NUMBER BETWEEN 1 AND 7) OR
                (T3.ROUTE_NUMBER >7 AND VALID_DATE>=dbo.FUNC_TO_DATE_TIME_INTEGER(GETDATE())))" : "AND T3.ROUTE_NUMBER=" + RouteNumber
                                                  );
        
            Query query = new Query(SQL);
            return SqlData.ExecuteReader(query);
        }



        /// <summary>
        /// 获取制定用户的历史拜访记录，返回不同日期（天）的访问次数，完成拜访次数，以及达完成访问比例
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public DataTable GetUserHistoryVisitList(int UserID)
        {
            string sql = string.Format(@"select top 10 *,cast(round((t3.fact*100/t3.visitNumber),2) as nvarchar)+'%' as fact_visit_percent  from (
select USER_ID,left(START_TIME,8)as visit_date,count(*) as visitNumber,
(select COUNT(*) from VISITS where STATUS=2 and USER_ID=t1.user_id and left(START_TIME,8)=left(t1.START_TIME,8)) as fact
from VISITS t1
where USER_ID={0} and  START_TIME is not null  and len(START_TIME)>8
group by USER_ID,left(START_TIME,8)
)
as t3  order by visit_date desc ", UserID);
            return SqlData.OpenSql(sql).Tables[0];
        }

        /// <summary>
        /// 获取摆放信息通过userid和访问日期
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public DataTable GetVisitListByUserAndDate(int UserId, String date)
        {
            string sql = string.Format(@"select b.NAME,a.* from VISITS a inner join CUSTOMERS b
                                        on a.CUSTOMER_ID = b.ID
                                        and  USER_ID={0}
                                        and LEFT(a.START_TIME,8) ='{1}'
                                        order by LEFT(a.START_TIME,8) desc", UserId, date);
            return SqlData.OpenSql(sql).Tables[0];
        }
    }
}
